IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SaveSrvrInstncSchedJobMSSQL' AND type = 'P')
     DROP PROCEDURE dbo.SaveSrvrInstncSchedJobMSSQL
GO

CREATE PROCEDURE dbo.SaveSrvrInstncSchedJobMSSQL
     @SrvrInstncId             int,
     @PackageExecutionId       bigint,
     @JobIdentifier            varchar(50),
     @JobName                  varchar(128),
     @JobEnabledFlag           tinyint,
     @JobDescription           varchar(1024),
     @OwnerName                varchar(128),
     @CategoryId               int,
     @CategoryName             varchar(128),
     @StartStepId              int,
     @NotifyLevelEventlogDesc  varchar(30),
     @NotifyLevelEmailDesc     varchar(30),
     @NotifyLevelNetsendDesc   varchar(30),
     @NotifyLevelPageDesc      varchar(30),
     @NotifyEmailOperator      varchar(128),
     @NotifyNetsendOperator    varchar(128),
     @NotifyPageOperator       varchar(128),
     @CrDt                     datetime,
     @ModDt                    datetime,
     @VersionNumber            int,
     @JobDeletedFlag           tinyint       = 0
AS
/*
 *
 * SaveSrvrInstncSchedJobMSSQL
 *
 * Purpose:      
 *
 * Inputs:       
 *
 * Assumes:      
 *
 * Effects:      
 *
 * Returns:      
 *
 * Note(s):      
 *
 * CfgCtrl Keys: None
 *
 * Author(s):    NOVA Concepts Ltd.
 *
 * Change Log:   Initial Release, 05/30/2009, 1.00.0000
 *
 */
BEGIN
     SET NOCOUNT ON
     SET XACT_ABORT ON

     DECLARE @JobIdForSrv int,
             @StartDt     datetime, 
             --@PEI         bigint,
             @RC          int,
             @NOW         datetime

     -- Get current date/time
     SELECT @NOW = CURRENT_TIMESTAMP 
     
     -- Condition Flags
     SELECT @JobEnabledFlag = CASE WHEN @JobEnabledFlag != 1 OR @JobEnabledFlag IS NULL THEN 0 ELSE 1 END,
            @JobDeletedFlag = CASE WHEN @JobDeletedFlag != 1 OR @JobDeletedFlag IS NULL THEN 0 ELSE 1 END

     -- Condition Input
     SELECT @JobDescription = ISNULL( @JobDescription, '' )
     
     -- Find out if we have the record already
     IF NOT EXISTS ( SELECT 'X'
                     FROM   dbo.SrvrInstncSchedJobMSSQL
                     WHERE  SrvrInstncId  = @SrvrInstncId 
                     AND    JobIdentifier = @JobIdentifier )
     BEGIN -- We do not have this record, so we will do an insert, we should
           -- Never have a delete here as well so we will not test for it.
          -- Get the job Id 
          SELECT @JobIdForSrv = ISNULL(MAX(SrvrInstncSchedJobId),0) + 1
          FROM   dbo.SrvrInstncSchedJobMSSQL
          WHERE  SrvrInstncId  = @SrvrInstncId 

          INSERT dbo.SrvrInstncSchedJobMSSQL
               ( SrvrInstncId,
                 SrvrInstncSchedJobId,
                 StartDt,
                 EndDt,
                 CurrentRecordFlag,
                 PackageExecutionId,
                 JobDeletedFlag,
                 JobIdentifier,
                 JobName,
                 JobEnabledFlag,
                 JobDescription,
                 OwnerName,
                 CategoryId,
                 CategoryName,
                 StartStepId,
                 NotifyLevelEventlogDesc,
                 NotifyLevelEmailDesc,
                 NotifyLevelNetsendDesc,
                 NotifyLevelPageDesc,
                 NotifyEmailOperator,
                 NotifyNetsendOperator,
                 NotifyPageOperator,
                 CrDt,
                 ModDt,
                 VersionNumber )
          SELECT @SrvrInstncId,
                 @JobIdForSrv,
                 @NOW, -- StartDt
                 '12/31/9999', -- EndDt
                 1, -- CurrentRecordFlag
                 @PackageExecutionId,
                 0, -- JobDeletedFlag
                 @JobIdentifier,
                 @JobName,
                 @JobEnabledFlag,
                 @JobDescription,
                 @OwnerName,
                 @CategoryId,
                 @CategoryName,
                 @StartStepId,
                 @NotifyLevelEventlogDesc,
                 @NotifyLevelEmailDesc,
                 @NotifyLevelNetsendDesc,
                 @NotifyLevelPageDesc,
                 @NotifyEmailOperator,
                 @NotifyNetsendOperator,
                 @NotifyPageOperator,
                 @CrDt,
                 @ModDt,
                 @VersionNumber 
     END -- End New insert
     ELSE
     BEGIN -- We have at least one copy of this record, so we may do an insert
          BEGIN TRANSACTION

          -- Get the job Id 
          SELECT @JobIdForSrv = SrvrInstncSchedJobId 
          FROM   dbo.SrvrInstncSchedJobMSSQL 
          WHERE  SrvrInstncId  = @SrvrInstncId 
          AND    JobIdentifier = @JobIdentifier

          -- Get the startdt for the current record, if we are here one should
          -- be available, we perform a little trick here as well, taking 
          -- advantage or the ORDER BY  and making sure we have the maximum
          -- StartDt
          SELECT @StartDt = StartDt
          FROM   dbo.SrvrInstncSchedJobMSSQL
          WHERE  SrvrInstncId         = @SrvrInstncId
          AND    SrvrInstncSchedJobId = @JobIdForSrv
          AND    CurrentRecordFlag    = 1
          ORDER BY StartDt

          ---- Get PEI for current record, we should have one if we are here..
          --SELECT @PEI = PackageExecutionId
          --FROM   dbo.SrvrInstncSchedJobMSSQL
          --WHERE  SrvrInstncId         = @SrvrInstncId 
          --AND    SrvrInstncSchedJobId = @JobIdForSrv
          --AND    CurrentRecordFlag    = 1

          -- Reset current record flag to zero and set the end date to now minus
          -- 3 ms, 3ms was chosen due to the granularity of the datetype, it does
          -- not handle a difference less than 3ms.  Therefore it ends right before
          -- the new record starts with no overlap.
          UPDATE dbo.SrvrInstncSchedJobMSSQL  
          SET    EndDt             = DATEADD( ms, -3, @NOW ),
                 CurrentRecordFlag = 0
          WHERE  SrvrInstncId         = @SrvrInstncId 
          AND    SrvrInstncSchedJobId = @JobIdForSrv
          AND    CurrentRecordFlag    = 1

          IF ( @JobDeletedFlag = 0 )
          BEGIN -- Do regular insert
               INSERT dbo.SrvrInstncSchedJobMSSQL
                    ( SrvrInstncId,
                      SrvrInstncSchedJobId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      JobDeletedFlag,
                      JobIdentifier,
                      JobName,
                      JobEnabledFlag,
                      JobDescription,
                      OwnerName,
                      CategoryId,
                      CategoryName,
                      StartStepId,
                      NotifyLevelEventlogDesc,
                      NotifyLevelEmailDesc,
                      NotifyLevelNetsendDesc,
                      NotifyLevelPageDesc,
                      NotifyEmailOperator,
                      NotifyNetsendOperator,
                      NotifyPageOperator,
                      CrDt,
                      ModDt,
                      VersionNumber )
               SELECT @SrvrInstncId,
                      @JobIdForSrv,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      0, -- JobDeletedFlag
                      @JobIdentifier,
                      @JobName,
                      @JobEnabledFlag,
                      @JobDescription,
                      @OwnerName,
                      @CategoryId,
                      @CategoryName,
                      @StartStepId,
                      @NotifyLevelEventlogDesc,
                      @NotifyLevelEmailDesc,
                      @NotifyLevelNetsendDesc,
                      @NotifyLevelPageDesc,
                      @NotifyEmailOperator,
                      @NotifyNetsendOperator,
                      @NotifyPageOperator,
                      @CrDt,
                      @ModDt,
                      @VersionNumber 
               FROM   dbo.SrvrInstncSchedJobMSSQL
               WHERE  SrvrInstncId         = @SrvrInstncId 
               AND    SrvrInstncSchedJobId = @JobIdForSrv
               AND    StartDt              = @StartDt
               -- Decide if there will be an insert
               AND  ( JobDeletedFlag = 1
                      --1 = 0 -- Placeholder, always false, don't want to insert on accident
                   OR ( @JobName                 != JobName )
                   OR ( @JobEnabledFlag          != JobEnabledFlag )
                   OR ( @JobDescription          != JobDescription )
                   OR ( @OwnerName               != OwnerName )
                   OR ( @CategoryId              != CategoryId )
                   OR ( @CategoryName            != CategoryName )
                   OR ( @StartStepId             != StartStepId )
                   OR ( @NotifyLevelEventlogDesc != NotifyLevelEventlogDesc )
                   OR ( @NotifyLevelEmailDesc    != NotifyLevelEmailDesc )
                   OR ( @NotifyLevelNetsendDesc  != NotifyLevelNetsendDesc )
                   OR ( @NotifyLevelPageDesc     != NotifyLevelPageDesc )
                   OR ( @NotifyEmailOperator     != NotifyEmailOperator )
                   OR ( @NotifyNetsendOperator   != NotifyNetsendOperator )
                   OR ( @NotifyPageOperator      != NotifyPageOperator )
                   OR ( @CrDt                    != CrDt )
                   OR ( @ModDt                   != ModDt )
                   OR ( @VersionNumber           != VersionNumber )
                    ) -- End of And-Or section

               SELECT @RC = @@ROWCOUNT 
          END -- End of regular checked insert
          ELSE
          BEGIN -- Do database deleted insert
               INSERT dbo.SrvrInstncSchedJobMSSQL
                    ( SrvrInstncId,
                      SrvrInstncSchedJobId,
                      StartDt,
                      EndDt,
                      CurrentRecordFlag,
                      PackageExecutionId,
                      JobDeletedFlag,
                      JobIdentifier,
                      JobName,
                      JobEnabledFlag,
                      JobDescription,
                      OwnerName,
                      CategoryId,
                      CategoryName,
                      StartStepId,
                      NotifyLevelEventlogDesc,
                      NotifyLevelEmailDesc,
                      NotifyLevelNetsendDesc,
                      NotifyLevelPageDesc,
                      NotifyEmailOperator,
                      NotifyNetsendOperator,
                      NotifyPageOperator,
                      CrDt,
                      ModDt,
                      VersionNumber )
               SELECT SrvrInstncId,
                      SrvrInstncSchedJobId,
                      @NOW, -- StartDt
                      '12/31/9999', -- EndDt
                      1, -- CurrentRecordFlag
                      @PackageExecutionId,
                      1, -- JobDeletedFlag
                      JobIdentifier,
                      JobName,
                      JobEnabledFlag,
                      JobDescription,
                      OwnerName,
                      CategoryId,
                      CategoryName,
                      StartStepId,
                      NotifyLevelEventlogDesc,
                      NotifyLevelEmailDesc,
                      NotifyLevelNetsendDesc,
                      NotifyLevelPageDesc,
                      NotifyEmailOperator,
                      NotifyNetsendOperator,
                      NotifyPageOperator,
                      CrDt,
                      ModDt,
                      VersionNumber 
               FROM   dbo.SrvrInstncSchedJobMSSQL 
               WHERE  SrvrInstncId         = @SrvrInstncId
               AND    SrvrInstncSchedJobId = @JobIdForSrv
               AND    StartDt              = @StartDt
               AND    JobDeletedFlag       = 0 -- Don't re-delete it..

               SELECT @RC = @@ROWCOUNT 
          END

          IF ( @RC = 1 )
          BEGIN -- We did do an insert so we commit it.
               COMMIT TRANSACTION
          END
          ELSE
          BEGIN -- We did not do any insert, this is not an error, but we will 
                -- do a rollback since we want to keep the last current record.
               ROLLBACK TRANSACTION
          END
     END -- End Existing Insert
END
GO

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Save a server instance scheduled job MS SQL specific record if anything has changed from the previous run, calls to this sproc will NOT always generate a new record.', @level0type = N'Schema', @level0name = dbo, @level1type = N'PROCEDURE', @level1name = SaveSrvrInstncSchedJobMSSQL;
GO
